package com.zhan.ielts.tools;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.aliyun.oss.ClientException;
import com.aliyun.oss.OSSException;
import com.zhan.frame.core.utils.AliyunOssUtil;
import com.zhan.frame.core.utils.EncryptUtile;
import com.zhan.frame.core.utils.FileUtils;
import com.zhan.ielts.domain.ZhentiComposition;

public class ZhentiCompositionUtils {
	private static Logger log = LoggerFactory.getLogger(ZhentiCompositionUtils.class);
	private static Connection conn = getConnection();

	public static void main(String[] args) throws Exception {
		log.info("start");
		String filepath = "/xiaozhan/received files/jiang qian/0330/2014~2016年雅思历年写作真题汇总(完整版)160408.xlsx";
		importZhentiComposition(filepath);

		String rootPath = "/xiaozhan/received files/jiang qian/0330/yun/";
		// exportZhentiComposition(rootPath);

		String yunPath = "test/ielts/ZhentiComposition/";
		// uploadZhentiComposition(rootPath, yunPath);

		log.info("end");
	}

	public static void importZhentiComposition(String filePath) {
		try {
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
			List<ZhentiComposition> list = new ArrayList<ZhentiComposition>();
			int count = 1;
			for (int k = 0; k < 3; k++) {

				XSSFSheet sheet = wb.getSheetAt(k);
				XSSFRow row = null;
				XSSFCell cell = null;

				for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++) {
					// for (int i = 0; i < 100; i++) {
					row = sheet.getRow(i);
					if (row.getCell(0) == null) {
						break;
					}
					System.out.println("k->" + k + "--i->" + i);
					String cell0 = "";
					if (row.getCell((short) 0).getCellType() == XSSFCell.CELL_TYPE_STRING) {
						cell0 = String.valueOf(row.getCell((short) 0).getStringCellValue());
					} else {
						cell0 = String.valueOf(row.getCell((short) 0).getRawValue());
					}
					if (StringUtils.isEmpty(cell0) || cell0.equals("null")) {
						break;
					}
					System.out.println(cell0);
					String examDate = cell0.substring(0, 8);
					String form = cell0.length() > 8 ? cell0.substring(8).trim() : "-";

					for (int j = 0; j < 2; j++) {
						ZhentiComposition zhentiComposition = new ZhentiComposition();
						if (j == 0) {
							zhentiComposition.setCompositionType("大作文");
						} else {
							zhentiComposition.setCompositionType("小作文");
						}

						zhentiComposition.setId(String.valueOf(count++));
						zhentiComposition.setYear(examDate.substring(0, 4));
						zhentiComposition.setMonth(examDate.substring(4, 6));
						zhentiComposition.setDay(examDate.substring(6, 8));
						zhentiComposition.setForm(form);
						zhentiComposition.setComposition(String.valueOf(row.getCell((short) 1 + j * 3)));
						zhentiComposition.setModelEssay(String.valueOf(row.getCell((short) 2 + j * 3)));
						zhentiComposition.setCategory(String.valueOf(row.getCell((short) 3 + j * 3)));
						zhentiComposition.setUpdateTime(System.currentTimeMillis());
						Thread.sleep(1);
						list.add(zhentiComposition);
					}

				}
			}

			updateZhentiComposition(list);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static void exportZhentiComposition(String filepath) throws Exception {
		List<ZhentiComposition> list = getZhentiComposition(0, Integer.MAX_VALUE);
		FileUtils.removeAllFile(filepath);
		if (list != null && list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				ZhentiComposition zhentiComposition = list.get(i);
				JSONObject jsonbject = new JSONObject();
				jsonbject.element("composition", zhentiComposition.getComposition());
				jsonbject.element("modelEssay", zhentiComposition.getModelEssay());
				// jsonbject.element("image", value);
				String filename = zhentiComposition.getYear()+zhentiComposition.getMonth()+zhentiComposition.getDay() + "_" + zhentiComposition.getForm() + "_"
						+ zhentiComposition.getCompositionType();
				// FileUtils.createFile(filepaht+filename,
				// jsonbject.toString());
				FileUtils.createFile(filepath + "encrypt_" + filename, EncryptUtile.encrypt(jsonbject.toString()));
			}

		}
	}

	public static void uploadZhentiComposition(String rootPath, String yunPath) {
		File folder = new File(rootPath);
		File[] files = folder.listFiles();
		if (files != null && files.length > 1) {
			for (int i = 0; i < files.length; i++) {
				try {
					AliyunOssUtil.uploadFile(files[i], yunPath);
				} catch (OSSException e) {
					e.printStackTrace();
				} catch (ClientException e) {
					e.printStackTrace();
				} catch (FileNotFoundException e) {
					e.printStackTrace();
				}
			}
		}

	}

	public static List<ZhentiComposition> getZhentiComposition(int from, int to) throws ClassNotFoundException,
			SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		// Connection conn = getConnection();
		if (conn == null) {
			conn = getConnection();
		}
		conn.setAutoCommit(false);
		stmt = conn
				.prepareStatement("select form, composition_type,composition, model_essay,category from t_zhenti_composition order by year, month, date, form, composition_type limit  "
						+ from + "," + to);
		rs = stmt.executeQuery();
		List<ZhentiComposition> list = new ArrayList<ZhentiComposition>();
		while (rs.next()) {
			ZhentiComposition zhentiComposition = new ZhentiComposition();
			zhentiComposition.setForm(rs.getString(1));
			zhentiComposition.setCompositionType(rs.getString(2));
			zhentiComposition.setComposition(rs.getString(3));
			zhentiComposition.setModelEssay(rs.getString(4));
			zhentiComposition.setCategory(rs.getString(5));

			list.add(zhentiComposition);
		}
		return list;
	}

	public static void updateZhentiComposition(List<ZhentiComposition> list) throws ClassNotFoundException,
			SQLException {
		if (list == null || list.size() == 0)
			return;

		// Connection conn = getConnection();
		if (conn == null) {
			conn = getConnection();
		}
		conn.setAutoCommit(false);
		PreparedStatement stmt = conn
				.prepareStatement("replace into t_zhenti_composition(id, form, composition_type,composition,model_essay,category, year, month, date, image_url, update_time)VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");

		for (int n = 0; n < list.size(); n++) {
			// System.out.println(list.get(n));
			ZhentiComposition zhentiComposition = list.get(n);
			stmt.setString(1, zhentiComposition.getId());
			stmt.setString(2, zhentiComposition.getForm().trim());
			stmt.setString(3, zhentiComposition.getCompositionType());
			stmt.setString(4, zhentiComposition.getComposition());
			stmt.setString(5, zhentiComposition.getModelEssay());
			stmt.setString(6, zhentiComposition.getCategory());
			stmt.setString(7, zhentiComposition.getYear());
			stmt.setString(8, zhentiComposition.getMonth());
			stmt.setString(9, zhentiComposition.getDay());
			stmt.setString(10, zhentiComposition.getImageUrl() == null ? "" : zhentiComposition.getImageUrl());
			stmt.setLong(11, zhentiComposition.getUpdateTime());
			stmt.addBatch();
		}

		stmt.executeBatch();
		conn.commit();
	}

	public static Connection getConnection() {
		String className = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://192.168.1.188:3306/ieltsdb?zeroDateTimeBehavior=convertToNull";
		String username = "ieltsdba";
		String userpass = "zaq1_XSW2";
		Connection con = null;
		try {
			Class.forName(className);
			con = DriverManager.getConnection(url, username, userpass);
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}

}
